# -*- coding: utf-8 -*-
import pandas as pd
import snap7
from snap7.util import *
import time
import os
import warnings

# 忽略SettingWithCopyWarning
warnings.filterwarnings("ignore", category=pd.errors.SettingWithCopyWarning)

# ======================== 全局配置参数 ========================
# Excel文件默认路径
EXCEL_DEFAULT_PATH = r'D:\迅雷下载\DB.xlsx'

# Excel必要列配置
EXCEL_REQUIRED_COLUMNS = ['DB', 'NAME', 'TYPE', 'OFFSET', 'GX']
PLC_CONNECT_TIMEOUT = 10000  # 10秒

# PLC地址配置
PLC_CONFIG = {
    'JY': {'ip': '192.168.0.120', 'rack': 0, 'slot': 1, 'description': '挤压工序'},  # 挤压工序PLC地址
    'CH': {'ip': '192.168.0.120', 'rack': 0, 'slot': 1, 'description': '淬火工序'},  # 淬火工序PLC地址
    'QYJ': {'ip': '192.168.0.120', 'rack': 0, 'slot': 1, 'description': '牵引机工序'},  # 牵引机工序PLC地址
    'LZ': {'ip': '192.168.0.120', 'rack': 0, 'slot': 1, 'description': '拉直工序'},  # 拉直工序PLC地址
    'JQ': {'ip': '192.168.0.120', 'rack': 0, 'slot': 1, 'description': '锯切工序'}   # 锯切工序PLC地址
}

# ======================== 数据类型映射 ========================
PLC_TYPE_MAPPING = {
    'DBD': ('REAL', 4),    # DB块双字，4字节
    'DBW': ('INT', 2),     # DB块字，2字节
    'DBX': ('BOOL', 1),    # DB块位，1字节
    'INT': ('INT', 2),
    'DINT': ('DINT', 4),
    'REAL': ('REAL', 4),
    'BOOL': ('BOOL', 1)
}

DATA_TYPE_HANDLERS = {
    'DBD': (4, lambda data, offset: get_real(data, offset)),
    'DBW': (2, lambda data, offset: get_int(data, offset)),
    'DBX': (1, lambda data, offset, bit: get_bool(data, offset, bit)),
    'INT': (2, lambda data, offset: get_int(data, offset)),
    'DINT': (4, lambda data, offset: get_dint(data, offset)),
    'REAL': (4, lambda data, offset: get_real(data, offset)),
    'BOOL': (1, lambda data, offset, bit: get_bool(data, offset, bit))
}

class PLCDataProcessor:
    def __init__(self):
        self.plc_clients = {}
        self.excel_data = None
        self.processed_data = {}

    def 加载Excel数据(self, file_path):
        """加载Excel表格数据并返回DataFrame"""
        try:
            import os
            if not os.path.exists(file_path):
                print(f"错误：Excel文件不存在 - {file_path}")
                return False
            try:
                self.excel_data = pd.read_excel(file_path, engine='openpyxl')
                print(f"成功加载Excel数据，共{len(self.excel_data)}条记录")
                return True
            except Exception as e:
                print(f"加载Excel失败: {str(e)}")
                return False
        except Exception as e:
            print(f"加载Excel失败: {str(e)}")
            return False

    def 获取数据类型信息(self, type_str):
        """处理TYPE列数据，返回PLC类型、字节大小和描述信息"""
        if pd.isna(type_str) or not str(type_str).strip():
            return {
                '原始类型': None,
                'PLC类型': '未知',
                '字节大小': 0,
                '描述': '类型为空'
            }
            
        type_str = str(type_str).strip().upper()
        
        # 在全局PLC_TYPE_MAPPING中查找类型
        if type_str in PLC_TYPE_MAPPING:
            plc_type, size = PLC_TYPE_MAPPING[type_str]
            return {
                '原始类型': type_str,
                'PLC类型': plc_type,
                '字节大小': size,
                '描述': f'类型: {type_str} -> {plc_type}({size}字节)'
            }
        else:
            return {
                '原始类型': type_str,
                'PLC类型': '未知',
                '字节大小': 0,
                '描述': f'未知类型: {type_str}'
            }

    def 获取类型描述(self, plc_type):
        """获取PLC数据类型的详细中文描述"""
        type_descriptions = {
            'REAL': '32位浮点数',
            'INT': '16位整数',
            'BOOL': '布尔值(1位)',
            'DINT': '32位整数',
            'DBD': 'DB块双字(32位)',
            'DBW': 'DB块字(16位)',
            'DBX': 'DB块位(1位)'
        }
        return type_descriptions.get(plc_type, '未知数据类型')

    def 按工序分组数据(self):
        """根据GX列将数据按工序分组"""
        if self.excel_data is None:
            print("请先加载Excel数据")
            return {}

        # 检查必要列是否存在
        required_columns = EXCEL_REQUIRED_COLUMNS
        if not set(required_columns).issubset(self.excel_data.columns):
            missing = set(required_columns) - set(self.excel_data.columns)
            print(f"Excel缺少必要列: {missing}")
            return {}

        # 按GX列分组
        grouped = self.excel_data.groupby('GX')
        return {gx: group for gx, group in grouped}

    def 判断区域类型(self, gx_code):
        """根据工序代码判断其所属的DB块或IQM区"""
        process_groups = self.按工序分组数据()
        if gx_code not in process_groups:
            print(f"错误：工序 {gx_code} 不存在数据")
            return None
        
        group_data = process_groups[gx_code]
        results = []
        
        for _, row in group_data.iterrows():
            db_value = row['DB']
            area_type = '未知'
            area_number = None
            
            if isinstance(db_value, str):
                db_value = db_value.strip().upper()
                # 匹配DB块格式 (DB+数字)
                if db_value.startswith('DB') and db_value[2:].isdigit():
                    area_type = 'DB'
                    area_number = db_value  # 保留完整DB块号如DB10
                # 匹配I区格式 (仅需I开头)
                elif db_value.startswith('I'):
                    area_type = 'I'
                    area_number = db_value
                # 匹配Q区格式 (仅需Q开头)
                elif db_value.startswith('Q'):
                    area_type = 'Q'
                    area_number = db_value
                # 匹配M区格式 (仅需M开头)
                elif db_value.startswith('M'):
                    area_type = 'M'
                    area_number = db_value
            
            results.append({
                'db_value': row['DB'],
                'area_type': area_type,
                'area_number': area_number
            })
        
        return results

    def 获取Excel文件路径(self):
        """获取用户输入的Excel文件路径并验证"""
        while True:
            default_path = EXCEL_DEFAULT_PATH
            input_path = input(f"请输入Excel文件路径(默认: {default_path})：").strip()
            excel_path = input_path if input_path else default_path
            
            if os.path.exists(excel_path):
                if os.path.isfile(excel_path) and excel_path.endswith(('.xlsx', '.xls')):
                    return excel_path
                else:
                    print(f"{excel_path} 不是有效的Excel文件")
            else:
                print(f"文件不存在: {excel_path}")
                retry = input("是否重试? (y/n): ").strip().lower()
                if retry != 'y':
                    print("程序已退出")
                    exit(1)

    def 获取偏移值信息(self, offset_str):
        """处理OFFSET列数据，返回偏移值和数据类型信息"""
        try:
            offset = int(offset_str)
            return {
                '偏移值': offset,
                '描述': f'偏移量: {offset}字节'
            }
        except ValueError:
            return {
                '偏移值': None,
                '描述': '无效的偏移量格式'
            }

    def 获取名称信息(self, name_str):
        """处理NAME列数据，返回名称和描述信息"""
        if pd.isna(name_str) or not str(name_str).strip():
            return {
                '名称': None,
                '描述': '名称为空'
            }
            
        name = str(name_str).strip()
        return {
            '名称': name,
            '描述': f'名称: {name}'
        }

    def 获取工序完整数据(self, process_code):
        """获取工序的完整数据信息，包括名称、DB块、类型、偏移量和描述"""
        process_data = self.按工序分组数据().get(process_code)
        if process_data is None:
            return None
            
        results = []
        for _, row in process_data.iterrows():
            name_info = self.获取名称信息(row['NAME'])
            type_info = self.获取数据类型信息(row['TYPE'])
            offset_info = self.获取偏移值信息(row['OFFSET'])
            
            results.append({
                '名称': name_info['名称'],
                'DB块': row['DB'],
                '类型': type_info['PLC类型'],
                '偏移量': offset_info['偏移值'],
                '字节大小': type_info['字节大小'],
                '描述': f"{name_info['描述']}, {type_info['描述']}, {offset_info['描述']}"
            })
        
        return results

    def 生成DB读取代码(self, process_code):
        """根据工序代码生成DB块读取代码"""
        process_data = self.按工序分组数据().get(process_code)
        if process_data is None or process_data.empty:
            return None

        code_lines = []
        # 按DB块分组处理
        db_groups = process_data.groupby('DB')

        for db, group in db_groups:
            if str(db).strip().upper().startswith('DB') and str(db)[2:].isdigit():
                db_number = int(str(db)[2:])
                
                # 创建临时副本进行预处理
                temp_data = group.copy()
                temp_data['TYPE_SIZE'] = temp_data['TYPE'].apply(
                    lambda t: PLC_TYPE_MAPPING.get(t.strip().upper(), (None, 0))[1])
                
                # 处理OFFSET列（支持浮点数值）
                temp_data['OFFSET_STR'] = temp_data['OFFSET'].astype(str)
                temp_data['BYTE_OFFSET'] = temp_data['OFFSET_STR'].apply(
                    lambda x: int(float(x.split('.')[0]))  # 取整数部分
                )
                temp_data['BIT_OFFSET'] = temp_data['OFFSET_STR'].apply(
                    lambda x: int(x.split('.')[1]) if '.' in x else 0
                )
                
                # 计算读取范围
                max_offset = int(temp_data['BYTE_OFFSET'].max())
                max_size = max_offset + max(temp_data['TYPE_SIZE'])
                
                code_lines.append(f"# 读取DB{db_number}数据，大小{max_size}字节")
                code_lines.append(f"data = plc.db_read({db_number}, 0, {max_size})")
                code_lines.append("")
                
                # 生成读取代码
                for _, row in temp_data.iterrows():
                    data_type = row['TYPE'].strip().upper()
                    if data_type in PLC_TYPE_MAPPING:
                        mapped_type, size = PLC_TYPE_MAPPING[data_type]
                        byte_offset = row['BYTE_OFFSET']
                        bit_offset = row['BIT_OFFSET']
                        name = row['NAME'] or f"DB{db_number}.DBB{byte_offset}.{bit_offset}"
                        
                        # 根据类型生成代码
                        code_lines.append(f"# {name} ({mapped_type})")
                        if mapped_type == 'INT':
                            code_lines.append(f"counter = get_int(data, {byte_offset})")
                        elif mapped_type == 'DINT':
                            code_lines.append(f"counter = get_dint(data, {byte_offset})")
                        elif mapped_type == 'REAL':
                            code_lines.append(f"counter = get_real(data, {byte_offset})")
                        elif mapped_type == 'BOOL':
                            code_lines.append(f"counter = get_bool(data, {byte_offset}, {bit_offset})")
                        
                        # 格式化输出
                        if mapped_type == 'REAL':
                            code_lines.append(f'print(f"{name}: {{counter:.2f}}")')
                        else:
                            code_lines.append(f'print(f"{name}: {{counter}}")')
                        code_lines.append("")

        return '\n'.join(code_lines)

    def 生成M区读取代码(self, process_code):
        """根据工序代码生成M区读取代码"""
        process_data = self.按工序分组数据().get(process_code)
        if process_data is None or process_data.empty:
            return None

        # 筛选DB列以'M'开头的数据（排除NaN值）
        m_data = process_data[process_data['DB'].str.startswith('M', na=False)].copy()
        if m_data.empty:
            print(f"工序 {process_code} 无M区数据")
            return None

        code_lines = []
        
        # 精确计算读取范围（每个变量的偏移量+自身类型大小）
        temp_data = m_data.copy()
        temp_data['TYPE_SIZE'] = temp_data['TYPE'].apply(lambda t: PLC_TYPE_MAPPING.get(t.strip().upper(), (None, 0))[1])
        
        # 处理OFFSET列（可能包含如500.1这样的值）
        temp_data['OFFSET_STR'] = temp_data['OFFSET'].astype(str)
        temp_data['BYTE_OFFSET'] = temp_data['OFFSET_STR'].apply(
            lambda x: int(float(x.split('.')[0]))  # 取整数部分
        )
        temp_data['BIT_OFFSET'] = temp_data['OFFSET_STR'].apply(
            lambda x: int(x.split('.')[1]) if '.' in x else 0
        )
        
        temp_data['END_ADDRESS'] = temp_data['BYTE_OFFSET'] + temp_data['TYPE_SIZE']
        read_size = int(temp_data['END_ADDRESS'].max())  # 读取到最大结束地址
        
        # 假设M区基址为0（即M0），根据实际情况调整
        m_base = 0
        
        code_lines.append(f"# 读取M区数据（覆盖到最大结束地址 {read_size}）")
        code_lines.append(f"data = plc.mb_read({m_base}, {read_size})")
        code_lines.append("")
        
        # 生成每个变量的读取代码
        for _, row in temp_data.iterrows():
            db_value = row['DB']  # DB列值（如M500）
            offset_str = row['OFFSET_STR']  # OFFSET列值（如500.1）
            data_type = row['TYPE'].strip().upper()
            name = row['NAME'] or f"{db_value}.{offset_str}"
            byte_offset = row['BYTE_OFFSET']
            bit_offset = row['BIT_OFFSET']
            
            if data_type not in PLC_TYPE_MAPPING:
                code_lines.append(f"# 跳过未知类型: {data_type}")
                continue
                
            mapped_type, size = PLC_TYPE_MAPPING[data_type]
            
            # 根据类型生成读取代码
            code_lines.append(f"# {name} ({mapped_type})")
            if mapped_type == 'BOOL':
                code_lines.append(f"value = get_bool(data, {byte_offset}, {bit_offset})")
            elif mapped_type == 'INT':
                code_lines.append(f"value = get_int(data, {byte_offset})")
            elif mapped_type == 'DINT':
                code_lines.append(f"value = get_dint(data, {byte_offset})")
            elif mapped_type == 'REAL':
                code_lines.append(f"value = get_real(data, {byte_offset})")
            else:
                code_lines.append(f"value = None # 未知类型: {mapped_type}")
            
            code_lines.append(f'print(f"{name}: {{value}}")')
            code_lines.append("")

        return '\n'.join(code_lines)

def 获取用户选择工序():
    """获取用户选择的工序代码"""
    print("\n可用工序代码：")
    for code, info in PLC_CONFIG.items():
        print(f"{code}: {info.get('description', '未定义')}")
    
    while True:
        selected = input("请输入要处理的工序代码(多个用逗号分隔，如JY,CH)：").strip()
        if not selected:
            print("请至少选择一个工序")
            continue
        
        selected_codes = [code.strip().upper() for code in selected.split(',')]
        invalid = [code for code in selected_codes if code not in PLC_CONFIG]
        
        if invalid:
            print(f"无效的工序代码: {','.join(invalid)}")
            continue
        
        return selected_codes

def test_plc_connection(ip_address, rack, slot):
    """测试PLC连接状态"""
    plc = snap7.client.Client()
    try:
        # 连接PLC
        plc.connect(ip_address, rack, slot)
        time.sleep(1)  # 短暂延迟确保连接稳定
        connected = plc.get_connected()
        status = '成功' if connected else '失败'
        print(f"PLC {ip_address} 连接状态: {status}")
        return connected
    except Exception as e:
        print(f"PLC {ip_address} 连接错误: {str(e)}")
        return False
    finally:
        # 确保断开连接
        if plc.get_connected():
            plc.disconnect()

if __name__ == '__main__':
    # 创建处理器实例
    processor = PLCDataProcessor()
    
    # 获取用户选择的工序
    selected_codes = 获取用户选择工序()
    print(f"您选择处理的工序: {','.join(selected_codes)}")
    
    # 获取Excel文件路径
    excel_path = EXCEL_DEFAULT_PATH
    print(f"使用默认Excel文件: {excel_path}")
    
    # 验证默认文件是否存在
    if not os.path.exists(excel_path):
        print(f"错误：默认Excel文件不存在 - {excel_path}")
        exit(1)
    if not os.path.isfile(excel_path) or not excel_path.endswith(('.xlsx', '.xls')):
        print(f"错误：默认路径不是有效的Excel文件 - {excel_path}")
        exit(1)
    
    # 加载Excel数据
    if not processor.加载Excel数据(excel_path):
        exit(1)
    
    # 按工序分组并筛选数据
    process_groups = processor.按工序分组数据()
    selected_groups = {gx: group for gx, group in process_groups.items() if gx in selected_codes}
    
    # 计算并打印筛选结果
    total_count = sum(len(group) for group in selected_groups.values())
    print(f"\n===== 工序数据处理结果 =====")
    print(f"您选择的工艺 {','.join(selected_codes)} 对应的数据量: {total_count} 条")
    for gx, group in selected_groups.items():
        print(f"工艺 {gx}: {len(group)} 条数据") 
        # 判断区域类型并统计
        area_results = processor.判断区域类型(gx)
        if area_results:
            # 统计I/Q/M区数量
            i_count = sum(1 for res in area_results if res['area_type'] == 'I')
            q_count = sum(1 for res in area_results if res['area_type'] == 'Q')
            m_count = sum(1 for res in area_results if res['area_type'] == 'M')
            
            # 统计DB块数量并按块号分组
            db_blocks = {} 
            for res in area_results:
                if res['area_type'] == 'DB' and res['area_number']:
                    db_blocks[res['area_number']] = db_blocks.get(res['area_number'], 0) + 1
            
            # 未知类型计数
            unknown_count = len(area_results) - i_count - q_count - m_count - sum(db_blocks.values())
            
            # 格式化DB块显示文本
            db_text = ', '.join([f'{db}({count}条)' for db, count in db_blocks.items()]) if db_blocks else '无'
            
            # 打印区域统计结果
            print(f"  区域统计: I区 {i_count} 条, Q区 {q_count} 条, M区 {m_count} 条")
            print(f"  DB块统计: {db_text}")
            if unknown_count > 0:
                print(f"  未知类型: {unknown_count} 条")
    if total_count == 0:
        print("提示: 未找到对应工艺的数据，请检查工序代码或Excel文件")
    
    # 打印每个选择工艺的完整信息
    for code in selected_codes:
        print(f"\n===== 工艺 {code} 的完整信息 ====")
        full_data = processor.获取工序完整数据(code)
        if full_data:
            for item in full_data:
                print(f"名称: {item['名称']}")
                print(f"DB块: {item['DB块']}")
                print(f"类型: {item['类型']}")
                print(f"偏移量: {item['偏移量']}")
                print(f"字节大小: {item['字节大小']}")
                print(f"描述: {item['描述']}")
                print("-" * 40)
        else:
            print(f"未找到工艺 {code} 的数据")
            
        # 生成并执行DB读取代码
        db_code = processor.生成DB读取代码(code)
        if db_code:
            print(f"\n===== 执行 {code} 工艺的PLC读取 ====")
            plc = snap7.client.Client()
            try:
                config = PLC_CONFIG.get(code, {})
                plc_ip = PLC_CONFIG[code]['ip']
                plc_rack = PLC_CONFIG[code]['rack']
                plc_slot = PLC_CONFIG[code]['slot']
                plc.connect(plc_ip, plc_rack, plc_slot)
                print(f"成功连接到{PLC_CONFIG[code]['description']}PLC: {plc_ip}")
                
                # 执行生成的代码
                print(f"执行以下读取代码:\n{db_code}")
                try:
                    print("开始执行DB区读取...")
                    exec(db_code)
                    print("PLC读取执行完成")
                except Exception as e:
                    print(f"执行错误: {str(e)}")
                
                # 验证数据读取
                if not plc.get_connected():
                    print(f"警告: {PLC_CONFIG[code]['description']}PLC连接已断开")
                
            except Exception as e:
                print(f"PLC读取错误: {str(e)}")
            finally:
                if plc.get_connected():
                    plc.disconnect()
            
        # 生成并执行M区读取代码
        m_code = processor.生成M区读取代码(code)
        if m_code:
            print(f"\n===== 执行 {code} 工艺的M区PLC读取 ====")
            try:
                # 确保PLC连接
                if not plc.get_connected():
                    plc.connect(plc_ip, plc_rack, plc_slot)
                
                print(f"执行以下M区读取代码:\n{m_code}")
                print("开始执行M区PLC读取...")
                exec(m_code)
                print("M区PLC读取执行完成")
            except Exception as e:
                print(f"M区读取执行错误: {str(e)}")
            finally:
                if plc.get_connected():
                    plc.disconnect()